package com.scrat.huzhenjie.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import com.scrat.huzhenjie.model.Account;
import com.scrat.huzhenjie.model.Backup;
import com.scrat.huzhenjie.model.Count;
import com.scrat.huzhenjie.model.Recording;

@Service(value = "huangChengShiDao")
public class HuangChengShiDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	protected static final Logger LOG = Logger.getLogger("huzhenjie");
	public List<Recording> getRecordings(long imei) {
		String sql = "SELECT _id,_year,_month,_date,_time,_weather,_mood,_address,_content,_pictureuri,_sounduri,_other FROM recording WHERE _imei=?";
		LOG.log(Level.INFO,sql);
		return jdbcTemplate.query(sql,new Object[]{imei}, new RecordingMapper());
	}
	
	public List<Recording> getRecordings(int from,int length,long imei) {
		String sql = "SELECT _id,_year,_month,_date,_time,_weather,_mood,_address,_content,_pictureuri,_sounduri,_other FROM recording WHERE _imei=? LIMIT ? , ?";
		LOG.log(Level.INFO,sql);
		return jdbcTemplate.query(sql, new Object[]{imei,from,length},new RecordingMapper());
	}
	
	public List<Recording> getRecordings(String[] q,String[] value,long imei){
		StringBuilder sqlSB = new StringBuilder("SELECT _id,_year,_month,_date,_time,_weather,_mood,_address,_content,_pictureuri,_sounduri,_other FROM recording WHERE ");
		for (String str : q) {
			sqlSB.append(str).append("=? AND ");
		}
		sqlSB.append(" _imei = ").append(imei);
		String sql = sqlSB.toString();
		LOG.log(Level.INFO,sql);
		return jdbcTemplate.query(sql, value, new RecordingMapper());
	}
	
	public List<Recording> getRecordings(String[] q,String[] value,int from,int length,long imei){
		StringBuilder sqlSB = new StringBuilder("SELECT _id,_year,_month,_date,_time,_weather,_mood,_address,_content,_pictureuri,_sounduri,_other FROM recording WHERE ");
		List<Object> array = new ArrayList<Object>();
		int tmp = 0;
		for (String str : q) {
			sqlSB.append(str).append("=? AND ");
			array.add(value[tmp]);
			tmp ++;
		}
		sqlSB.append(" _imei = ").append(imei).append(" LIMIT ? , ?");
		String sql = sqlSB.toString();
		array.add(from);
		array.add(length);
		LOG.log(Level.INFO,sql);
		return jdbcTemplate.query(sql, array.toArray(), new RecordingMapper());
	}
	
	public List<Recording> getRecordings(String querySQL,int from,int length){
		StringBuilder sqlSB = new StringBuilder("SELECT _id,_year,_month,_date,_time,_weather,_mood,_address,_content,_pictureuri,_sounduri,_other FROM recording WHERE ")
											.append(querySQL).append(" LIMIT ? , ?");
		String sql = sqlSB.toString();
		LOG.log(Level.INFO,sql);
		return jdbcTemplate.query(sql, new Object[]{from,length}, new RecordingMapper());
	}
	
	public List<Recording> getRecordings(String querySQL){
		StringBuilder sqlSB = new StringBuilder("SELECT _id,_year,_month,_date,_time,_weather,_mood,_address,_content,_pictureuri,_sounduri,_other FROM recording WHERE ") 
											.append(querySQL);
		String sql = sqlSB.toString();
		LOG.log(Level.INFO,sql);
		return jdbcTemplate.query(sql, new RecordingMapper());
	}
	
	public void updateRecording(int id,int year,int month,String date,String time,String weather,String mood,String address,String content,String pictureuri,String sounduri,String other,long imei) {
		String sql = "UPDATE recording SET _year = ?,_month = ?,_date = ?,_time = ?,_weather = ?,_mood = ?,_address = ?,_content = ?,_pictureuri = ?,_sounduri = ?,_other = ? WHERE _id = ? AND _imei = ?";
		LOG.log(Level.INFO,sql);
		jdbcTemplate.update(sql,new Object[]{year,month,date,time,weather,mood,address,content,pictureuri,sounduri,other,id,imei});
	}
	
	public void addRecording(int year,int month,String date,String time,String weather,String mood,String address,String content,String pictureuri,String sounduri,String other,long imei) {
		String sql = "INSERT INTO recording SET _year = ?,_month = ?,_date = ?,_time = ?,_weather = ?,_mood = ?,_address = ?,_content = ?,_pictureuri = ?,_sounduri = ?,_other = ?,_imei = ?";
		LOG.log(Level.INFO,sql);
		jdbcTemplate.update(sql,new Object[]{year,month,date,time,weather,mood,address,content,pictureuri,sounduri,other,imei});
	}
	
	public int getTotalRecording(long imei){
		String sql = "SELECT COUNT(_id) FROM recording WHERE _imei = ?";
		LOG.log(Level.INFO,sql);
		return jdbcTemplate.queryForInt(sql,new Object[]{imei});
	}
	
	public void delRecording(int id,long imei){
		String sql = "DELETE FROM recording WHERE _id = ? AND _imei = ?";
		LOG.log(Level.INFO,sql);
		jdbcTemplate.update(sql,new Object[]{id,imei});
	}
	
	public void delRecording(long imei){
		String sql = "DELETE FROM recording WHERE _imei = ?";
		LOG.log(Level.INFO,sql);
		jdbcTemplate.update(sql,new Object[]{imei});
	}
	
	public List<Count> getCountRecording(long imei){
		String sql = "SELECT _year AS result ,COUNT(_id) AS total FROM recording WHERE _imei = ? GROUP BY _year";
		LOG.log(Level.INFO,sql);
		return jdbcTemplate.query(sql, new Object[]{imei},new CountMapper());
	}
	
	public List<Count> getCountRecording(long imei,int year){
		String sql = "SELECT _month AS result ,COUNT(_id) AS total FROM recording WHERE _imei = ? AND _year = ? GROUP BY _month";
		LOG.log(Level.INFO,sql);
		return jdbcTemplate.query(sql, new Object[]{imei,year},new CountMapper());
	}
	
	/*----------------------------------华丽分界线-------------------------------------------------*/
	public List<Backup> getbBackups(long imei) {
		String sql = "SELECT _id,_name,_time,_uri FROM backup WHERE _imei = ?";
		LOG.log(Level.INFO,sql);
		return jdbcTemplate.query(sql, new Object[]{imei}, new BackupMapper());
	}
	
	public void addBackup(String name,String time,String uri,long imei) {
		String sql = "INSERT INTO backup SET _name = ?,_time = ?,_uri = ?,_imei = ?";
		LOG.log(Level.INFO,sql);
		jdbcTemplate.update(sql,new Object[]{name,time,uri,imei});
	}
	/*----------------------------------华丽分界线-------------------------------------------------*/
	public Account getAccount(long imei) {
		String sql = "SELECT _id,_imei,_password,_time,_name,_size,_tips,_other,_email FROM account WHERE _imei = ?";
		LOG.log(Level.INFO,sql);
		return jdbcTemplate.queryForObject(sql, new Object[]{imei}, new AccountMapper());
	}
	
	public void register(long imei,String password,String name,int size,String time,String tips,String other,String email) {
		String sql = "INSERT INTO account SET _imei = ?,_password = ?,_name = ?,_size = ?,_time = ?,_tips = ?,_other = ?,_email = ?";
		LOG.log(Level.INFO,sql);
		jdbcTemplate.update(sql,new Object[]{imei,password,name,size,time,tips,other,email});
	}
	
	public void updateAccount(long imei,String password,String name,int size,String time,String tips,String other,String email) {
		String sql = "UPDATE account SET _password = ?,_name = ?,_size = ?,_time = ?,_tips = ?,_other = ?,_email = ? WHERE _imei = ?";
		LOG.log(Level.INFO,sql);
		jdbcTemplate.update(sql,new Object[]{password,name,size,time,tips,other,email,imei});
	}
	/*----------------------------------华丽分界线-------------------------------------------------*/
	private class RecordingMapper implements RowMapper<Recording>{
		@Override
		public Recording mapRow(ResultSet rs, int arg1) throws SQLException {
			Recording recording = new Recording();
			recording.setId(rs.getInt("_id"));
			recording.setYear(rs.getInt("_year"));
			recording.setMonth(rs.getInt("_month"));
			recording.setDate(rs.getString("_date"));
			recording.setTime(rs.getString("_time"));
			recording.setWeather(rs.getString("_weather"));
			recording.setMood(rs.getString("_mood"));
			recording.setAddress(rs.getString("_address"));
			recording.setContent(rs.getString("_content"));
			recording.setPictureuri(rs.getString("_pictureuri"));
			recording.setSounduri(rs.getString("_sounduri"));
			recording.setOther(rs.getString("_other"));
			return recording;
		}
	}
	
	private class BackupMapper implements RowMapper<Backup>{
		@Override
		public Backup mapRow(ResultSet rs, int arg1) throws SQLException {
			Backup backup = new Backup();
			backup.setId(rs.getInt("_id"));
			backup.setName(rs.getString("_name"));
			backup.setTime(rs.getString("_time"));
			backup.setUri(rs.getString("_uri"));
			return backup;
		}
	}
	
	private class AccountMapper implements RowMapper<Account>{
		@Override
		public Account mapRow(ResultSet rs, int arg1) throws SQLException {
			Account account = new Account();
			account.setId(rs.getInt("_id"));
			account.setImei(rs.getLong("_imei"));
			account.setName(rs.getString("_name"));
			account.setOther(rs.getString("_other"));
			account.setPassword(rs.getString("_password"));
			account.setSize(rs.getInt("_size"));
			account.setTime(rs.getString("_time"));
			account.setTips(rs.getString("_tips"));
			account.setEmail(rs.getString("_email"));
			return account;
		}
	}
	
	private class CountMapper implements RowMapper<Count>{
		@Override
		public Count mapRow(ResultSet rs, int arg1) throws SQLException {
			Count count = new Count();
			count.setResult(rs.getInt("result"));
			count.setTotal(rs.getInt("total"));
			return count;
		}
	}
}
